﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class TuyenBayDAO
    {
        public List<TuyenBayDTO> LayTuyenBay()
        {
            String sql = "Select * from TuyenBay";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<TuyenBayDTO> dsTuyenBay = new List<TuyenBayDTO>();
            while (reader.Read())
            {
                TuyenBayDTO tbdto = new TuyenBayDTO();
                tbdto.MaTB = reader.GetString(0);
                tbdto.SanBayDi = reader.GetString(1);
                tbdto.SanBayDen = reader.GetString(2);
                dsTuyenBay.Add(tbdto);
            }
            conn.Close();
            return dsTuyenBay;
        }

        public TuyenBayDTO LayMotTuyenBay(string matb)
        {
            String sql = "Select * from TuyenBay where MaTuyenBay='" + matb + "'";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            TuyenBayDTO tbdto = new TuyenBayDTO();
            while (reader.Read())
            {
                tbdto.MaTB = reader.GetString(0);
                tbdto.SanBayDi = reader.GetString(1);
                tbdto.SanBayDen = reader.GetString(2);
            }
            conn.Close();
            return tbdto;
        }

        public int ThemTuyenBay(TuyenBayDTO tbdto)
        {
            String sql = string.Format("insert into TuyenBay values ('{0}',N'{1}',N'{2}') ",tbdto.MaTB, tbdto.SanBayDi,tbdto.SanBayDen);
            //insert into TuyenBay values ('DN-HN',N'Đà Nẵng',N'Hà Nội')
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            return res;
        }

        public int XoaTuyenBay(TuyenBayDTO tbdto)
        {
            String sql = "delete from TuyenBay where MaTuyenBay='" + tbdto.MaTB + "'";
            // DELETE FROM table_name WHERE some_column=some_value
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            return res;
        }
    }
}
